USE Partitioning;
GO

-- show the table partitions and rows inserted in the partitions
SELECT
	p.partition_number,
	fg.name,
	p.rows,
	p.index_id,
	au.total_pages,
	au.used_pages,
	p.data_compression_desc
FROM sys.partitions p
    INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')
AND p.index_id < 2;

SET DATEFORMAT mdy;
GO

-- create the partition function and scheme
DECLARE @InitialPartitionDate DATE = '01/01/2009';

CREATE PARTITION FUNCTION pfOrders (DATETIME)
AS RANGE RIGHT FOR VALUES (@InitialPartitionDate);

CREATE PARTITION SCHEME psOrders
AS PARTITION pfOrders
TO ( [SECONDARY], [PRIMARY] );
GO

-- recreate the clustered index against the partition scheme
CREATE CLUSTERED INDEX CX_Orders_OrderDate ON dbo.Orders
(
	orderdate
)
WITH (DROP_EXISTING = ON) ON psOrders (orderdate);
GO

-- align non-clustered indexes with partition scheme
CREATE NONCLUSTERED INDEX IX_Orders_ShipperID_OrderDate ON dbo.Orders 
(
	shipperid ASC,
	orderdate ASC
)
INCLUDE (custid) WITH (DROP_EXISTING = ON) ON psOrders(orderdate);

CREATE UNIQUE NONCLUSTERED INDEX UX_Orders_OrderDate_OrderID ON dbo.Orders 
(
	orderdate ASC,
	orderid ASC
)
INCLUDE (custid, empid) WITH (DROP_EXISTING = ON) ON psOrders(orderdate);

-- verify the partitioned indexes in the Orders table
SELECT
	OBJECT_NAME(ips.[object_id]) AS 'object_name',
	i.name AS index_name,
	ips.index_id,
	ips.partition_number,
	ips.index_type_desc,
	ips.alloc_unit_type_desc,
	ips.page_count
FROM sys.dm_db_index_physical_stats (
	DB_ID('Partitioning'),
	OBJECT_ID(N'dbo.Orders'),
	NULL,
	NULL,
	'LIMITED') ips
CROSS APPLY Partitioning.sys.indexes i
WHERE i.object_id = ips.object_id
AND i.index_id = ips.index_id
ORDER BY index_name, partition_number ASC;

-- show the table partitions and rows inserted in the partitions
SELECT
	p.partition_number,
	fg.name,
	p.rows,
	p.index_id,
	au.total_pages,
	au.used_pages,
	p.data_compression_desc
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')
AND p.index_id < 2;

-- rebuild an index on partition level
ALTER INDEX UX_Orders_OrderDate_OrderID ON dbo.Orders
REBUILD PARTITION = 1;

-- enable data compression on partition level
-- ALTER TABLE dbo.Orders REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = ROW);
ALTER TABLE dbo.Orders REBUILD PARTITION = 1
	WITH (DATA_COMPRESSION = PAGE);

-- set filegroup read-only for archived data
/*
USE master;
GO

ALTER DATABASE Partitioning SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE Partitioning 
MODIFY FILEGROUP [SECONDARY] READONLY;

ALTER DATABASE Partitioning SET MULTI_USER;
*/

-- backup the filegroup
/*
BACKUP DATABASE Partitioning
	FILEGROUP = N'SECONDARY' 
TO DISK = N'C:\MSSQL\Backup\PartitioningSecondary.bak'
GO
*/

USE Partitioning;
GO

-- show the table partitions and rows inserted in the partitions
SELECT
	p.partition_number,
	fg.name,
	p.rows,
	p.index_id,
	au.total_pages,
	au.used_pages,
	p.data_compression_desc
FROM sys.partitions p
    INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Orders')
AND p.index_id < 2;

-- verify rows
DECLARE @OrderDate DATETIME = '01/01/2009';

SELECT 'PRODUCTION' AS name, COUNT(*) AS rows
FROM dbo.Orders WHERE orderdate >= @OrderDate
UNION
SELECT 'ARCHIVE' AS name, COUNT(*) AS rows
FROM dbo.Orders WHERE orderdate < @OrderDate;

-- list out which tables are partitioned, and what partition scheme and partition function they use
SELECT
	t.name AS table_name,
	ps.name AS partition_scheme, 
	ps.data_space_id,
	pf.name AS partition_function,
	pf.function_id
FROM sys.tables t
	INNER JOIN sys.indexes i ON t.object_id = i.object_id
	INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
	INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.index_id < 2;

-- for a given partition function, list each of the boundary values
SELECT
	prv.boundary_id,
	prv.value
FROM sys.partition_range_values prv
	INNER JOIN sys.partition_functions pf ON prv.function_id = pf.function_id
WHERE pf.name = 'pfOrders'		-- partition function name
ORDER BY prv.value;

-- run the following query and display the showplan again
SET STATISTICS XML ON;
GO

SELECT * FROM dbo.Orders
WHERE orderdate >= '20100101';

SET STATISTICS XML OFF;
GO

-- in the xml representation you should see something similar to this
/*
<Object Database="[Partitioning]" Schema="[dbo]" Table="[Orders]" Index="[idx_cl_od]" IndexKind="Clustered" />
<SeekPredicates>
  <SeekPredicateNew>
    <SeekKeys>
      <Prefix ScanType="EQ">
        <RangeColumns>
          <ColumnReference Column="PtnId1000" />
        </RangeColumns>
        <RangeExpressions>
          <ScalarOperator ScalarString="(2)">
            <Const ConstValue="(2)" />
          </ScalarOperator>
        </RangeExpressions>
      </Prefix>
      <StartRange ScanType="GE">
        <RangeColumns>
          <ColumnReference Database="[Partitioning]" Schema="[dbo]" Table="[Orders]" Column="orderdate" />
        </RangeColumns>
        <RangeExpressions>
          <ScalarOperator ScalarString="'2009-01-01 00:00:00.000'">
            <Const ConstValue="'2010-01-01 00:00:00.000'" />
          </ScalarOperator>
        </RangeExpressions>
      </StartRange>
    </SeekKeys>
  </SeekPredicateNew>
</SeekPredicates>
*/

-- after dropping the clustered index the table is still partitioned
/*
DROP INDEX idx_cl_od ON dbo.Orders;
GO

SELECT OBJECT_NAME(object_id),*
FROM sys.partitions
WHERE OBJECT_NAME(object_id) = 'Orders';
GO
*/